package com.yangcsoft.common.utils;

import com.yangcsoft.common.exception.RRException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by hhkmac on 2017/6/5.
 */
@Component

public class ExcelUtil {
    private ExcelUtil() {

    }

    /**
     * excel头部标题
     *
     * @param title
     * @param cellRangeAddressLength
     * @return
     */
    public static HSSFWorkbook makeExcelHead(String title, int cellRangeAddressLength) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle styleTitle = createStyle(workbook, (short) 16);
        HSSFSheet sheet = workbook.createSheet(title);
        sheet.setDefaultColumnWidth(25);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cellRangeAddressLength);
        sheet.addMergedRegion(cellRangeAddress);
        HSSFRow rowTitle = sheet.createRow(0);
        HSSFCell cellTitle = rowTitle.createCell(0);
        // 为标题设置背景颜色
        styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellTitle.setCellValue(title);
        cellTitle.setCellStyle(styleTitle);
        return workbook;
    }

    /**
     * 设定二级标题
     *
     * @param workbook
     * @param secondTitles
     * @return
     */
    public static HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles) {
        // 创建用户属性栏
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow rowField = sheet.createRow(1);
        HSSFCellStyle styleField = createStyle(workbook, (short) 13);
        for (int i = 0; i < secondTitles.length; i++) {
            HSSFCell cell = rowField.createCell(i);
            cell.setCellValue(secondTitles[i]);
            cell.setCellStyle(styleField);
        }
        return workbook;
    }

    /**
     * 插入数据
     *
     * @param workbook
     * @param dataList
     * @param beanPropertys
     * @return
     */
    public static <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<T> dataList, String[] beanPropertys) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 填充数据
        HSSFCellStyle styleData = workbook.createCellStyle();
        styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        for (int j = 0; j < dataList.size(); j++) {
            HSSFRow rowData = sheet.createRow(j + 2);
            T t = dataList.get(j);
            for (int k = 0; k < beanPropertys.length; k++) {
                Object value = org.apache.commons.beanutils.BeanUtils.getProperty(t, beanPropertys[k]);
                if (StringUtils.isEmpty(value)) {
                    value = "";
                }
                if (StringUtils.isEmpty(value) || value.toString().length() < 6) {
                    sheet.setColumnWidth(k, 8 * 512);
                } else {
                    //判断有几个双字节的字符，以便下面能够准确的设置宽度
                    int a = value.toString().getBytes().length - value.toString().length();
                    sheet.setColumnWidth(k, value.toString().length() * 512 + a * 512);
                }
                HSSFCell cellData = rowData.createCell(k);
                cellData.setCellValue(value.toString());

                cellData.setCellStyle(styleData);
            }
        }
        return workbook;
    }

    /**
     * 使用批量导入方法时，请注意需要导入的Bean的字段和excel的列一一对应
     *
     * @param clazz
     * @param file
     * @param beanPropertys
     * @return
     */
    public static <T> List<T> parserExcel(Class<T> clazz, MultipartFile file, String[] beanPropertys) {
        // 得到workbook
        List<T> list = new ArrayList<T>();
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            // 直接从第三行开始获取数据
            int rowSize = sheet.getPhysicalNumberOfRows();
            if (rowSize > 2) {
                for (int i = 2; i < rowSize; i++) {
                    boolean flag = false;
                    T t = clazz.newInstance();
                    Row row = sheet.getRow(i);
                    int cellSize = row.getLastCellNum();
                    if (cellSize > beanPropertys.length) {
                        throw new RRException("ERRORTTEMPLATE");
                    }
                    for (int j = 0; j < cellSize; j++) {

                        Object cellValue = getCellValue(row.getCell(j));
                        org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);
                        if(!StringUtils.isEmpty(cellValue)){
                            flag = true;
                        }
                    }
                    if(flag){
                        list.add(t);
                    }


                }
            }

        } catch (Exception e) {
            if ("ERRORTTEMPLATE".equals(e.getMessage())) {
                throw new RRException("ERRORTTEMPLATE");
            }
            e.printStackTrace();
        }
        return list;

    }

    public static <T> List<T> parserExcel(Class<T> clazz, MultipartFile file, String[] beanPropertys, int startRow) {
        // 得到workbook
        List<T> list = new ArrayList<T>();
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            // 直接从第三行开始获取数据
            int rowSize = sheet.getPhysicalNumberOfRows();
            if (rowSize > startRow) {
                for (int i = startRow; i < rowSize; i++) {
                    T t = clazz.newInstance();
                    Row row = sheet.getRow(i);
                    int cellSize = row.getLastCellNum();
                    if (cellSize > beanPropertys.length) {
                        throw new RRException("ERRORTTEMPLATE");
                    }
                    for (int j = 0; j < cellSize; j++) {

                        Object cellValue = getCellValue(row.getCell(j));
//                        ConvertUtils.register(new DateLocaleConverter(), Date.class);
                        org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);
                    }
                    boolean b = checkObjAllFieldsIsNull(t);
                    if (!b) {
                        list.add(t);
                    }

                }
            }

        } catch (Exception e) {
            if ("ERRORTTEMPLATE".equals(e.getMessage())) {
                throw new RRException("ERRORTTEMPLATE");
            }
            e.printStackTrace();
        }
        return list;

    }

    /**
     * 判断对象中属性值是否全为空
     *
     * @param o
     * @return
     */
    public static boolean checkObjAllFieldsIsNull(Object o) {
        if (null == o) {
            return true;
        }
        try {
            for (Field field : o.getClass().getDeclaredFields()) {
                field.setAccessible(true);

                Object object = field.get(o);
                if (object instanceof CharSequence) {
                    if (!org.springframework.util.ObjectUtils.isEmpty(object)) {
                        return false;
                    }
                } else {
                    if (null != object) {
                        return false;
                    }
                }
            }
        } catch (Exception e) {

        }
        return true;
    }


    /**
     * 通用的读取excel单元格的处理方法
     *
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        Object result = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //对日期进行判断和解析
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double cellValue = cell.getNumericCellValue();
                        result = HSSFDateUtil.getJavaDate(cellValue);
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        result = df.format(cell.getNumericCellValue());
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        }
        return result;
    }

    /**
     * 提取公共的样式
     *
     * @param workbook
     * @param fontSize
     * @return
     */
    private static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 创建一个字体样式
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        return style;
    }


    public static void download(HttpServletRequest request, HttpServletResponse response, String filePath) throws Exception {
        response.setContentType("text/html;charset=UTF-8");
        request.setCharacterEncoding("UTF-8");
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;


        File file = new File(filePath);
        long fileLength = file.length();

        response.setContentType("application/x-download");
        response.setHeader("Content-disposition",
                "attachment; filename=" + new String(file.getName().getBytes("utf-8"), "ISO8859-1"));
        response.setHeader("Content-Length", String.valueOf(fileLength));

        bis = new BufferedInputStream(new FileInputStream(filePath));
        OutputStream out = response.getOutputStream();
        bos = new BufferedOutputStream(out);
        byte[] buff = new byte[2048];
        int bytesRead;
        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
            bos.write(buff, 0, bytesRead);
        }
        bis.close();
        bos.close();
    }

    public static File exportResult(HttpServletRequest request, List dataList, String title, String[] secondTitles, String[] beanProperty) {

        try {
            HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead(title, secondTitles.length - 1);
            HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);

            HSSFWorkbook workbook = null;
            try {
                workbook = ExcelUtil.exportExcelData(workbook2, dataList, beanProperty);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            String ctxPath = request.getSession().getServletContext().getRealPath("/") + "/upload/temp/";
            File f = new File(ctxPath);
            if(!f.exists()){
                f.mkdirs();
            }
            File file = new File(ctxPath + title + ".xls");
            if (!file.exists()) {
                file.createNewFile();
            }
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            return file;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param workbook workbook.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public static HSSFWorkbook setHSSFValidation(HSSFWorkbook workbook, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        // 加载下拉列表内容
        HSSFSheet sheet = workbook.getSheetAt(0);
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return workbook;
    }

    public static HSSFWorkbook createWorkBook(HttpServletResponse response,String filename,String title,String[] secondTitles){
        try {
            response.setContentType("application/x-download");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gbk"), "iso8859-1") + ".xls");
            HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead(title, secondTitles.length - 1);
            return workbook1;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 模版下载
     *
     * @param response
     * @param filename
     * @param title
     * @param secondTitles
     */
    public static void templateDownload(HttpServletResponse response, String filename, String title, String[] secondTitles, List<String> datas, int startRow, int endRow, int startCol, int endCol) {

        try {
            response.setContentType("application/x-download");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gbk"), "iso8859-1") + ".xls");
            HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead(title, secondTitles.length - 1);


//            workbook2 = setHSSFValidation(workbook2, textlist, 2, 5000, 12, 12);

            HSSFWorkbook workbook = dropDownList2003(workbook1, workbook1.getSheetAt(0), datas, startRow, endRow, startCol, endCol, "hidden", 1);

            HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook, secondTitles);
            OutputStream out = response.getOutputStream();
            workbook2.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static HSSFWorkbook setValidationCell(HSSFWorkbook workbook, String[] textlist, int firstRow, int endRow, int firstCol, int endCol){
        try {
            workbook = ExcelUtil.setHSSFValidation(workbook, textlist,firstRow,endRow,firstCol,endCol);
            workbook = dropDownList2003(workbook, workbook.getSheetAt(0), Arrays.asList(textlist), firstRow, endRow, firstCol, endCol, "hidden", 1);
            return workbook;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 模版下载
     *
     * @param response
     */
    public static void templateDownload(HttpServletResponse response,HSSFWorkbook workbook1,String[] secondTitles,  int startRow, int endRow, int startCol, int endCol ) {

        try {

            HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
            OutputStream out = response.getOutputStream();
            workbook2.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param wb               HSSFWorkbook对象
     * @param realSheet        需要操作的sheet对象
     * @param datas            下拉的列表数据
     * @param startRow         开始行
     * @param endRow           结束行
     * @param startCol         开始列
     * @param endCol           结束列
     * @param hiddenSheetName  隐藏的sheet名
     * @param hiddenSheetIndex 隐藏的sheet索引
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook dropDownList2003(Workbook wb, Sheet realSheet, List<String> datas, int startRow, int endRow,
                                                int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex) throws Exception {

        HSSFWorkbook workbook = (HSSFWorkbook) wb;
        // 创建一个数据源sheet
        HSSFSheet hidden = workbook.createSheet(hiddenSheetName);
        // 数据源sheet页不显示
        workbook.setSheetHidden(hiddenSheetIndex, true);
        // 将下拉列表的数据放在数据源sheet上
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = 0; i < datas.size(); i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas.get(i));
        }
        //2016-12-15更新，遇到问题：生成的excel下拉框还是可以手动编辑，不满足
        //HSSFName namedCell = workbook.createName();
        //namedCell.setNameName(hiddenSheetName);
        // A1 到 Adatas.length 表示第一列的第一行到datas.length行，需要与前一步生成的隐藏的数据源sheet数据位置对应
        //namedCell.setRefersToFormula(hiddenSheetName + "!$A$1:$A" + datas.length);
        // 指定下拉数据时，给定目标数据范围 hiddenSheetName!$A$1:$A5   隐藏sheet的A1到A5格的数据
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$A$1:$A" + datas.size());
        org.apache.poi.hssf.util.CellRangeAddressList addressList = null;
        HSSFDataValidation validation = null;
        row = null;
        cell = null;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();

        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 循环指定单元格下拉数据
        if (startRow == 0 && endRow == 0 && startCol == 0 && endCol == 0) {
            return workbook;
        } else {
            for (int i = startRow; i <= endRow; i++) {
                row = (HSSFRow) realSheet.createRow(i);
                cell = row.createCell(startCol);
                cell.setCellStyle(style);
                addressList = new org.apache.poi.hssf.util.CellRangeAddressList(i, i, startCol, endCol);
                validation = new HSSFDataValidation(addressList, constraint);
                realSheet.addValidationData(validation);
            }
            workbook.removeSheetAt(1);
        }

        return workbook;
    }

    public static XSSFWorkbook dropDownList42007(String modelNmae, String[] modelHead, List<String> data,
                                                 int startRow, int endRow, int startCol, int endCol)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(modelNmae);
        Row row = sheet.createRow(0);
        CellStyle style = CatalogExcelUtil.getHeadStyle(workbook);
        for (int i = 0; i < modelHead.length; i++) {
            CatalogExcelUtil.initCell(row.createCell(i), style, modelHead[i]);
        }

        Object[] datas = data.toArray();
        String[] datas1 = new String[datas.length];
        for (int i = 0; i < datas.length; i++) {
            datas1[i] = datas[i].toString();
        }
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(datas1);
        org.apache.poi.hssf.util.CellRangeAddressList addressList = null;
        XSSFDataValidation validation = null;
        if (startRow == 0 && endRow == 0 && startCol == 0 && endCol == 0) {
            return workbook;
        } else {
            for (int i = startRow; i < endRow; i++) {
                addressList = new org.apache.poi.hssf.util.CellRangeAddressList(i, i, startCol, endCol);
                validation = (XSSFDataValidation) dvHelper.createValidation(
                        dvConstraint, addressList);
                // 07默认setSuppressDropDownArrow(true);
                // validation.setSuppressDropDownArrow(true);
                // validation.setShowErrorBox(true);
                sheet.addValidationData(validation);
            }
        }
        return workbook;
    }

}
